set doc off
set verify off
--
--    Program: CAPGLEXT_RATE_DAILY.sql
--    Description:
--       Extract daily rate associated with the SOB that the user specified
--
--    Revision:
--       1st     
--    Parameter: 1. set of books short name e.g. SOB_C999

--whenever sqlerror exit failure rollback
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
set serveroutput on size 1000000;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
   
   cursor cur_s (p_ngo_sob varchar2) is
		select * 
		from gl_daily_rates 
		where conversion_type IN (select conversion_type 
		                         from gl_daily_conversion_types 
		                         where substr(user_conversion_type, 1, 4) = substr(p_ngo_sob, -4));
      
begin
   l_pathname := '/tmp';
   l_filename := 'CAPGL_RATE_DAILY.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := UPPER('&1');
       
   for s in cur_s(l_ngo_sob) loop
     
     utl_file.put_line( l_utl_file, 
       s.FROM_CURRENCY     ||chr(9)||
       s.TO_CURRENCY       ||chr(9)||
       s.CONVERSION_DATE   ||chr(9)||
       s.CONVERSION_TYPE   ||chr(9)||
       s.CONVERSION_RATE   ||chr(9)||
       s.STATUS_CODE       ||chr(9)||
       s.CREATION_DATE     ||chr(9)||
       s.CREATED_BY        ||chr(9)||
       s.LAST_UPDATE_DATE  ||chr(9)||
       s.LAST_UPDATED_BY   ||chr(9)||
       s.LAST_UPDATE_LOGIN ||chr(9)||
       s.CONTEXT           ||chr(9)||
       s.ATTRIBUTE1        ||chr(9)||
       s.ATTRIBUTE2        ||chr(9)||
       s.ATTRIBUTE3        ||chr(9)||
       s.ATTRIBUTE4        ||chr(9)||
       s.ATTRIBUTE5        ||chr(9)||
       s.ATTRIBUTE6        ||chr(9)||
       s.ATTRIBUTE7        ||chr(9)||
       s.ATTRIBUTE8        ||chr(9)||
       s.ATTRIBUTE9        ||chr(9)||
       s.ATTRIBUTE10       ||chr(9)||
       s.ATTRIBUTE11       ||chr(9)||
       s.ATTRIBUTE12       ||chr(9)||
       s.ATTRIBUTE13       ||chr(9)||
       s.ATTRIBUTE14       ||chr(9)||
       s.ATTRIBUTE15       ||chr(9)||
       CAPBREXT_COMMON.GetUser(s.CREATED_BY)||chr(9)||
       CAPBREXT_COMMON.GetUser(s.LAST_UPDATED_BY)
     );
     
     l_processed_count := l_processed_count + 1;

   end loop;       
   
   if l_processed_count = 0 then
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := 'No Daily Rate defined for this NGO '||l_ngo_sob;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPGLEXT_RATE_DAILY', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);  
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
